from pyecharts import options as opts
from pyecharts.render import make_snapshot
from snapshot_phantomjs import snapshot
import numpy as np
from pyecharts.charts import Funnel

from pyecharts.faker import Faker

# data = [
#     list(z) for z in zip(Faker.choose(), Faker.values())
# ]
data = []
# print(data)
# print("______________________________________")
def funnel_chart() -> Funnel:
    # ********* Begin *********#
    global data
    funnel = (
        Funnel()
        .add("商品",data)
        .set_global_opts(
        title_opts=opts.TitleOpts(title="Funnel-基本示例",subtitle="Funnel-副标题"))
    )
    # ********** End **********#
    return funnel

def test():
    global data
    import pandas as pd
    from sqlalchemy import create_engine
    # 初始化数据库连接，使用pymysql模块
    # MySQL的用户：root, 密码:147369, 端口：3306,数据库：test
    engine = create_engine('mysql+pymysql://root:123456@localhost:3306/pythondata')
    # 查询语句，选出employee表中的所有数据
    sql = ''' select  city,count(*) as count from test group by city ORDER BY count ASC;'''
    # read_sql_query的两个参数: sql语句， 数据库连接
    city = pd.read_sql_query(sql, engine)
    # 输出employee表的查询结果
    data = np.array(city)  # 先将数据框转换为数组
    data = data.tolist()  # 其次转换为列表
test()
make_snapshot(snapshot, funnel_chart().render("漏斗图1.html"), 'student_answer.png') # 输出图片